{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ab9b477b-5c4d-4ac6-818f-955bdf3a48fc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       A      B      C      D\n",
      "0  False  False  False  False\n",
      "1  False  False  False  False\n",
      "2   True  False  False   True\n",
      "3  False  False  False   True\n",
      "A    1\n",
      "B    0\n",
      "C    0\n",
      "D    2\n",
      "dtype: int64\n",
      "     A  B  C   D\n",
      "2  NaN  4  7 NaN\n",
      "3  4.0  5  8 NaN\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A  B  C    D\n",
       "0  1.0  3  5  7.0\n",
       "1  2.0  4  6  5.0"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#数据清洗\n",
    "#缺失值的处理\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "na_df = pd.DataFrame({'A':[1, 2, np.NaN, 4],\n",
    "                     'B': [3, 4, 4, 5],\n",
    "                     'C': [5, 6, 7, 8],\n",
    "                     'D': [7, 5, np.NaN, np.NaN]})\n",
    "#使用isna() 检测na_df是否包含缺失值\n",
    "print(na_df.isna())\n",
    "\n",
    "#计算每列缺失值的总和\n",
    "print(na_df.isnull().sum())\n",
    "\n",
    "#查看缺失值所在的行\n",
    "print(na_df[na_df.isnull().T.any()])\n",
    "\n",
    "#高亮缺失值（只能在jupyter中实现）\n",
    "highlight_df = na_df.style.highlight_null('skyblue')\n",
    "#保存为html文件\n",
    "highlight_df.to_html('highlight_na_df.html')\n",
    "\n",
    "#删除缺失值\n",
    "na_df.dropna()\n",
    "\n",
    "#保留至少有3个非NaN的行\n",
    "na_df.drop\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5bd9b774-8521-4c1f-bde4-589822b1b35f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7f596294-9535-4602-a026-926d957ca01a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.3</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A  B  C    D\n",
       "0  1.0  3  5  7.0\n",
       "1  2.0  4  6  5.0\n",
       "2  2.3  4  7  6.0\n",
       "3  4.0  5  8  6.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 缺失值补全 | 平均数填充到指定的列\n",
    "# 计算A列的平均数，并保留一位小数\n",
    "col_a = np.around(np.mean(na_df['A']), 1)\n",
    "# 计算D列的平均数，并保留一位小数\n",
    "col_d = np.around(np.mean(na_df['D']), 1)\n",
    "# 将计算的平均数填充到指定的列\n",
    "na_df.fillna({'A':col_a, 'D':col_d})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3f7cf25-9146-4ddd-b3f9-0ae76bc98ba5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 缺失值补全 | 上下值填充\n",
    "na_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e04623ca-b754-470b-a6ff-3fa35dce1c91",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A  B  C    D\n",
       "0  1.0  3  5  7.0\n",
       "1  2.0  4  6  5.0\n",
       "2  3.0  4  7  5.0\n",
       "3  4.0  5  8  5.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 缺失值补全 | 线性插值\n",
    "na_df.interpolate(method='linear')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2f5c53b8-6f27-464e-a1c2-4c8e2fb28441",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  name  age  height gender\n",
      "0  刘婷婷   24     162      女\n",
      "1   王淼   23     165      女\n",
      "2   彭岩   29     175      男\n",
      "3   刘华   22     175      男\n",
      "4   刘华   22     175      男\n",
      "5   周华   20     178      男\n",
      "  name  age  height gender\n",
      "4   刘华   22     175      男\n",
      "  name  age  height gender\n",
      "1   王淼   23     165      女\n",
      "3   刘华   22     175      男\n",
      "4   刘华   22     175      男\n",
      "5   周华   20     178      男\n",
      "  name  age  height gender\n",
      "0  刘婷婷   24     162      女\n",
      "1   王淼   23     165      女\n",
      "2   彭岩   29     175      男\n",
      "3   刘华   22     175      男\n",
      "5   周华   20     178      男\n",
      "  name  age  height gender\n",
      "0  刘婷婷   24     162      女\n",
      "1   王淼   23     165      女\n",
      "2   彭岩   29     175      男\n",
      "4   刘华   22     175      男\n",
      "5   周华   20     178      男\n"
     ]
    }
   ],
   "source": [
    "# 创建DataFrame对象\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.DataFrame({'name': ['刘婷婷', '王淼', '彭岩', '刘华', '刘华', '周华'],\n",
    "                  'age': [24, 23, 29, 22, 22, 20],\n",
    "                  'height': [162, 165, 175, 175, 175, 178],\n",
    "                  'gender': ['女', '女', '男', '男', '男', '男']})\n",
    "print(df)\n",
    "\n",
    "#通过duplicated用来检测df对象中的重复值，返回值为boolean数组\n",
    "# 检测df对象中的重复值\n",
    "print(df[df.duplicated()])\n",
    "\n",
    "#查找重复列 | 指定\n",
    "#上面的所有列完全重复的情况，但有时我们只需要依据末列查找重复值\n",
    "print(df[df.duplicated(['gender'])])\n",
    "\n",
    "#删除重复值 --删除全部的重复值\n",
    "print(df.drop_duplicates())\n",
    "\n",
    "#删除重复值 | 但是保留最后一次出现的值\n",
    "print(df.drop_duplicates(keep = 'last'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90994509-263f-4f42-8627-fd41c5c17525",
   "metadata": {},
   "outputs": [],
   "source": [
    "import scipy.stats as stats\n",
    "data = pd.read_excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "be1e9e57-ca46-44ae-b3e1-127fc9c1900e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        name  old  weight\n",
      "id1    user1   21     121\n",
      "id2    user2   21     122\n",
      "id3    user3   20     132\n",
      "id4    user4   19     135\n",
      "id5    user5   23     128\n",
      "id6    user6   24     124\n",
      "id7    user7   22     129\n",
      "id8    user8   18     133\n",
      "id9    user9   19     362\n",
      "id10  user10   20     135\n",
      "id11  user11   20     128\n",
      "id12  user12   23     124\n",
      "id13  user13   22     129\n",
      "id14  user14   20     135\n",
      "id15  user15   22     128\n",
      "id16  user16   20     124\n",
      "id17  user17   19     129\n",
      "id18  user18   23      73\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "KstestResult(statistic=0.19828086515991383, pvalue=0.42381598406837895, statistic_location=20, statistic_sign=1)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "data = {'name': ['user1', 'user2', 'user3', 'user4', 'user5', 'user6', 'user7', 'user8', 'user9', 'user10', 'user11', 'user12', 'user13', 'user14', 'user15', 'user16', 'user17', 'user18'],\n",
    "       'old': [21, 21, 20, 19, 23, 24, 22, 18, 19, 20, 20, 23, 22, 20, 22, 20, 19, 23],\n",
    "       'weight': [121, 122, 132, 135, 128, 124, 129, 133, 362, 135, 128, 124, 129, 135, 128, 124, 129, 73]}\n",
    "\n",
    "columns1 = ['name', 'old', 'weight']\n",
    "index1 = ['id1', 'id2', 'id3', 'id4', 'id5', 'id6', 'id7', 'id8', 'id9', 'id10', 'id11', 'id12', 'id13', 'id14', 'id15', 'id16', 'id17', 'id18', ]\n",
    "\n",
    "df1 = pd.DataFrame(data, columns=columns1, index=index1)\n",
    "print(df1)\n",
    "\n",
    "# 基于3 sigma原则进行异常值检测\n",
    "# 判断异常值是否符合正态分布\n",
    "import scipy.stats as stats\n",
    "data = df1\n",
    "u = data['old'].mean()    #计算均值\n",
    "std = data['old'].std()    #计算标准差\n",
    "stats.kstest(data['old'], 'norm', (u, std))    #检测是否符合正态分布"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc103036-9b30-496a-84aa-e5b2eb1b1980",
   "metadata": {},
   "outputs": [],
   "source": [
    "def three_sigma(ser):\n",
    "    \"\"\"\n",
    "    :param ser: 被检测的数据，接受DataFrame的一系列数据\n",
    "    :return: 异常值及其对应的行索引\n",
    "    \"\"\"\n",
    "    #计算平均值\n",
    "    mean_data = ser.mean()\n",
    "    #计算标准差\n",
    "    std_data = ser.std()\n",
    "    print(\"平均值mean_data: {},标准差std_data: {}\".format(mean_data, std_data))\n",
    "    #"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a16c086-c56b-41e8-991c-04698d03c90c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#删除知道那个索引的行后，查看异常值情况\n",
    "df1_data = df1.drop()['id1']\n",
    "three_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f86509f4-c056-4a15-925b-cd0584412c01",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 绘制箱形图，查看有无异常值\n",
    "import matplotlib.pyplot as plt\n",
    "impport matplotlib\n",
    "\n",
    "#这句只能在jupter写\n",
    "%%matplotlib inline    \n",
    "font = {\n",
    "    'family': 'SimHei',\n",
    "    'weight': 'bold',\n",
    "    'size': 12\n",
    "}\n",
    "matplotlib.rc(\"font\", **font)\n",
    "matplotlib.rcParams['axea.unicode_minus']=False\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cd9d3f2f-6e2c-4261-b1fb-949dd0ab3f6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#返回异常值\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "def box_outliers(ser):\n",
    "    #对检测的数据集进行排序\n",
    "    new_strt = ser.sort_values()\n",
    "    #判断数据的总数量是奇数还是偶数\n",
    "    if new_ser.count() % 2 == 0：\n",
    "        #计算Q3、Q1、IQR\n",
    "        Q3 = new_str[int(len(new_ser) / 2):].median()\n",
    "        Q1 = new_str[int(len(new_ser) / 2)].median()\n",
    "    elif new_ser.count() % 2 != 0:\n",
    "        Q3 = new_str[int(len(new_ser) / 2):].median()\n",
    "        Q1 = new_str[:int((len(new_ser)-1) / 2)].median()\n",
    "    IQR = round(Q3 - Q1, 1)\n",
    "    ma = round(Q3+1.5*IQR, 1)\n",
    "    mi = round(Q1-1.5*IQR, 1)\n",
    "\n",
    "    rule = (ma < ser)|(mi > ser)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "78a871db-03db-4328-89b1-2b05a6888751",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'box_outliers' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[14], line 3\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[38;5;66;03m#删除指定索引后，查看异常情况\u001b[39;00m\n\u001b[0;32m      2\u001b[0m df1_drop \u001b[38;5;241m=\u001b[39m df1\u001b[38;5;241m.\u001b[39mdrop([\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mid1\u001b[39m\u001b[38;5;124m'\u001b[39m])\n\u001b[1;32m----> 3\u001b[0m box_outliers(df1_drop[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mold\u001b[39m\u001b[38;5;124m'\u001b[39m])\n",
      "\u001b[1;31mNameError\u001b[0m: name 'box_outliers' is not defined"
     ]
    }
   ],
   "source": [
    "#删除指定索引后，查看异常情况\n",
    "df1_drop = df1.drop(['id1'])\n",
    "box_outliers(df1_drop['old'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2732ffa-8e66-45b8-bba6-954b6ffd945d",
   "metadata": {},
   "outputs": [],
   "source": [
    "topnum1 = 26.5\n",
    "bottomnum1 = 14.5\n",
    "\n",
    "replace_value1 = df1['old']"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
